//
//  SqliteManager.swift
//  app路由
//
//  Created by zhaoguoteng on 2020/4/29.
//  Copyright © 2020 zhaoguoteng. All rights reserved.
//

import FMDB
import UIKit

class SqliteManager {
    static let kDBTableScanlog = "scanlog"
    static let kDBTableRoutes = "routes"
    
    static let share = SqliteManager()
    private var db: FMDatabase?
    private var dbQueue: FMDatabaseQueue?
    
    init() {
        guard let libraryDir = NSSearchPathForDirectoriesInDomains(.libraryDirectory, .userDomainMask, true).last else {
            return
        }
        
        print(libraryDir)
        
        let sqlitePathUrl = URL(fileURLWithPath: libraryDir).appendingPathComponent("app-route.sqlite")
        
        db = FMDatabase(url: sqlitePathUrl)
        dbQueue = FMDatabaseQueue(url: sqlitePathUrl)
    }
    
    class var shareDB: FMDatabase? {
        return SqliteManager.share.db
    }
    
    class var shareDBQ: FMDatabaseQueue? {
        return SqliteManager.share.dbQueue
    }
    
    /// 创建表
    class func createTable() {
        guard let dbQueue = SqliteManager.share.dbQueue else {
            return
        }
        
        DispatchQueue.global(qos: .default).async {
            dbQueue.inTransaction { (db: FMDatabase, _: UnsafeMutablePointer<ObjCBool>) in
                do {
                    if !db.tableExists("scanlog") {
                        try db.executeUpdate("create table scanlog (rid integer PRIMARY KEY autoincrement, json text, date double)", values: nil)
                    }
                    if !db.tableExists("routes") {
                        try db.executeUpdate("create table routes (rid integer PRIMARY KEY autoincrement, name text, url text, remark text, img blob, date double)", values: nil)
                    }
                } catch {
                    fatalError(error.localizedDescription)
                }
            }
        }
    }
    
    /// 查询数据
    class func queryData<T: Codable>(sql: String?, result: (([T]) -> Void)?) {
        guard let sql = sql else {
            return
        }
        
        guard let dbQueue = SqliteManager.share.dbQueue else {
            return
        }
                
        DispatchQueue.global(qos: .default).async {
            dbQueue.inTransaction { (db: FMDatabase, rollback: UnsafeMutablePointer<ObjCBool>) in
                do {
                    let resSet = try db.executeQuery(sql, values: nil)
                    
                    var list = [T]()
                    while resSet.next() {
                        if let dict = resSet.resultDictionary as? [String: Any] {
                            let jsonData = try! JSONSerialization.data(withJSONObject: dict, options: .fragmentsAllowed)
                            let t = try! JSONDecoder().decode(T.self, from: jsonData)
                            list.append(t)
                        }
                    }
                    
                    if let result = result {
                        OperationQueue.main.addOperation {
                            result(list)
                        }
                    }
                    
                } catch {
                    print(error.localizedDescription)
                    //                rollback.initialize(to: true)
                    rollback.pointee = true
                }
            }
        }
    }
    
    /// 插入数据
    class func insertData() {}
}
